package com.njcb.ams.store.text.transform;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Hashtable;
import java.util.List;
import java.util.Vector;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * 文本导入到数据库
 * 
 * @author 刘彦龙
 */
public class TextToTable {

	private static final Logger logger = LoggerFactory.getLogger(TextToTable.class);
	// 根据条件组装的INSERT INTO 语句
	private StringBuffer insertSql = new StringBuffer();
	// 数据库表的字段定义集合
	private List<ColumnInfo> tableFieldList = new ArrayList<ColumnInfo>();
	// 数据文件中定义的字段名
	private List<String> fileFieldList = new ArrayList<String>();
	// 数据字段的默认值、解析字段(fileFieldList)中不存在时填充
	private Hashtable<String, String> staticData = new Hashtable<String, String>();
	private PreparedStatement pstatement = null;
	private Connection conn = null;
	private ImpDataBase dataCheck = null;

	// 要导入的数据文件名称
	private String fileName = "";
	// 要导入的数据库表名称
	private String tableName = "";
	// 文件字段分割字符
	private String sepertor = "\\|";
	// 文件编码格式
	private String charsetName = "UTF-8";
	// 数据库序列的对象名称
	private String sequence = null;
	// 文件数据中的日期格式(解析成数据库DATE时使用)
	private String datePattern = "yyyyMMdd";
	// 批量提交条数,为0时则不分批(一次性提交)
	private int prtCount = 1000;

	public TextToTable() {
	}

	public TextToTable(Connection conn, String tableName, String fileName) {
		this.conn = conn;
		this.tableName = tableName.toUpperCase();
		this.fileName = fileName;
	}

	private void init() throws Exception {
		initInsertSql();
		pstatement = conn.prepareStatement(insertSql.toString());
	}

	public void setSequenceForID(String sequence) {
		this.sequence = sequence;
	}

	/**
	 * 批量提交，不支持断点处理
	 * 
	 * @return 数据条数及有效条数
	 * @throws Exception
	 */
	public int[] doImportProcess() throws Exception {
		long startTime = System.currentTimeMillis();
		long useTime = 0L;

		init();

		if (fileFieldList.size() == 0) {
			throw new Exception("fileFieldList无数据字段");
		}
		if (tableFieldList.size() == 0) {
			throw new Exception("tableFieldList无数据字段");
		}
		BufferedReader in = null;
		int lineCount = 0;
		int validCount = 0;
		try {
			in = new BufferedReader(new InputStreamReader(new FileInputStream(fileName), charsetName));
			Hashtable<String, String> map = new Hashtable<String, String>();
			logger.debug("开始处理 {}", fileName);
			while (true) {
				String lineData = in.readLine();
				if (lineData == null) {
					break;
				}
				lineCount++;
				// String[] values = split(lineData, sepertor, true);
				// //采用字符串自带的分割方法
				String[] values = lineData.split(sepertor, -1);
				map.clear();

				try {
					arrayValuesToHashtable(values, fileFieldList, map);
					if (dataCheck != null) {
						if (dataCheck.dataCheckAndConvert(map) == UtilsCommon.CHECK_RETURN_CODE_FAIL) {
							continue;
						}
					}
					setValues2PreparedStatement(map);
					pstatement.executeUpdate();
					validCount++;
				} catch (Exception e) {
					logger.error(e.getMessage(), e);
					throw new Exception("文件 [" + fileName + "] 行数 [" + lineCount + "]解析错误" + e.getMessage());
				}
				if (prtCount != 0 && lineCount % prtCount == 0) {
					conn.commit();
					logger.debug("已处理行数 {} @时间 {} ", lineCount, new Date());
				}
			}
			conn.commit();
			logger.info("总处理行数 {} 有效条数 {} @时间 {} ", lineCount, validCount, new Date());
		} finally {
			if (null != in) {
				in.close();
			}
			if (null != pstatement) {
				pstatement.close();
			}
		}

		useTime = System.currentTimeMillis() - startTime;
		logger.info("耗时: {} 分 {} 秒 {} 毫秒", useTime / 60000, useTime / 1000, useTime % 1000);
		return new int[] { lineCount, validCount };
	}

	/**
	 * 把文件中是数据映射到DataMap中
	 * 
	 * @param values
	 * @param fields
	 * @param map
	 */
	private void arrayValuesToHashtable(String[] values, List<String> fields, Hashtable<String, String> map) {
		for (int i = 0; i < fields.size(); i++) {
			String fieldName = fields.get(i);
			String value = values[i].trim();
			map.put(fieldName.toUpperCase(), value);
		}
	}

	/**
	 * 把DataMap中是值转化填充到插入数据中
	 * 
	 * @param map
	 * @throws Exception
	 */
	private void setValues2PreparedStatement(Hashtable<String, String> map) throws Exception {
		pstatement.clearParameters();
		int paramIndex = 0;
		for (int i = 0; i < tableFieldList.size(); i++) {
			ColumnInfo columnInfo = (ColumnInfo) tableFieldList.get(i);
			paramIndex++;
			if ("id".equalsIgnoreCase(columnInfo.getColumnName()) && !UtilsCommon.isEmpty(sequence)) {
				paramIndex--;
				continue;
			}

			String value = "";
			int indexOfLine = fileFieldList.indexOf(columnInfo.getColumnName());
			if (indexOfLine < 0) {
				value = getStaticData(columnInfo.getColumnName());// .trim();
			} else {
				value = map.get(columnInfo.getColumnName());// .trim();
			}
			if (UtilsCommon.isEmpty(value)) {
				value = map.get(columnInfo.getColumnName());
			}
			try {
				switch (columnInfo.getDataType()) {
				case java.sql.Types.INTEGER:
				case java.sql.Types.SMALLINT:
					pstatement.setInt(paramIndex, Integer.parseInt(value));
					break;
				case java.sql.Types.DECIMAL:
				case java.sql.Types.NUMERIC:
					if (UtilsCommon.isEmpty(value)) {
						value = "0";
					}
					pstatement.setDouble(paramIndex, Double.parseDouble(value));
					break;
				case java.sql.Types.CHAR:
				case java.sql.Types.VARCHAR:
					pstatement.setString(paramIndex, value);
					break;
				case java.sql.Types.DATE:
					pstatement.setDate(paramIndex, new java.sql.Date(stringToDate(value).getTime()));
					break;
				case java.sql.Types.TIMESTAMP:
					pstatement.setTimestamp(paramIndex, new Timestamp(System.currentTimeMillis()));
					break;
				default:
					throw new Exception("没有匹配的字段类型: COLUMN=" + columnInfo.getColumnName().toUpperCase() + ", " + "DATA_TYPE=" + columnInfo.getDataType() + ", " + "TYPE_NAME="
							+ columnInfo.getTypeName() + ", " + "VALUE=" + value);
				}
			} catch (Exception e) {
				logger.error(e.getMessage(), e);
				throw e;
			}
		}
	}

	/**
	 * 按照条件初始删除数据
	 * 
	 * @param where
	 * @throws Exception
	 */
	public void deleteData(String where) throws Exception {
		Statement stmt = null;
		String deleteSql = "delete from " + tableName + " where " + where;
		try {
			stmt = conn.createStatement();
			int deleteCount = stmt.executeUpdate(deleteSql);
			logger.debug("删除总行数 {} @时间 {}", deleteCount, new Date());
			conn.commit();
		} finally {
			if (null != stmt) {
				stmt.close();
			}
		}
	}

	public void truncateData() throws Exception {
		Statement stmt = null;
		String truncateSql = "truncate table " + tableName + " ";
		String countSql = "select count (*) from " + tableName + " ";
		int deleteCount = 0;
		try {
			stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(countSql);
			if (rs.next()){
				deleteCount = rs.getInt(1);
			}
			stmt.executeUpdate(truncateSql);
			logger.debug("删除总行数 {} @时间 {}", deleteCount, new Date());
			conn.commit();
		} finally {
			if (null != stmt) {
				stmt.close();
			}
		}
	}

	/**
	 * @return 初始化SQL
	 * @throws Exception
	 */
	private void initInsertSql() throws Exception {
		DatabaseMetaData dbMetaData = conn.getMetaData();
		ResultSet rsColumns = null;
		try {
			insertSql.setLength(0);
			rsColumns = dbMetaData.getColumns(null, null, tableName.toUpperCase(), null);
			insertSql.append("insert into " + tableName + " (");
			StringBuffer columnNames = new StringBuffer();
			int columnCount = 0;
			while (rsColumns.next()) {
				String columnName = rsColumns.getString("COLUMN_NAME").trim().toUpperCase();
				if (columnCount > 0) {
					columnNames.append(",");
				}
				columnCount++;
				columnNames.append(columnName);
				String typeName = rsColumns.getString("TYPE_NAME");
				int dataType = rsColumns.getInt("DATA_TYPE");
				ColumnInfo columnInfo = new ColumnInfo(columnName, typeName, dataType);
				tableFieldList.add(columnInfo);
			}
			if (0 == columnCount) {
				throw new Exception("数据表[" + tableName + "]不存在");
			}
			insertSql.append(columnNames).append(") values(");
			for (int i = 0; i < tableFieldList.size(); i++) {
				if (i > 0) {
					insertSql.append(",");
				}
				ColumnInfo columnInfo = (ColumnInfo) tableFieldList.get(i);
				if ("id".equalsIgnoreCase(columnInfo.getColumnName()) && !UtilsCommon.isEmpty(sequence)) {
					insertSql.append(sequence + ".nextval");
				} else {
					insertSql.append("?");
				}
			}
			insertSql.append(")");
			columnNames.setLength(0);
			columnNames = null;
		} finally {
			if (null != rsColumns) {
				rsColumns.close();
			}
		}
	}

	public String[] split(String value, String regex) {
		return split(value, regex, false);
	}

	public String[] split(String value, String regex, boolean addNull) {
		Vector<String> vector = new Vector<String>();
		String tmp = "";
		int marks = 0;
		boolean added = false;
		for (int i = 0; i < value.length(); i++) {
			added = false;
			char ch = value.charAt(i);
			boolean isInRegex = false;
			for (int j = 0; j < regex.length(); j++) {
				if (regex.charAt(j) == ch) {
					isInRegex = true;
					break;
				}
			}
			if (isInRegex == true) {
				if (marks != 0 && marks % 2 != 0) {
					tmp += value.charAt(i);
					continue;
				}

				if (addNull == true) {
					added = true;
					vector.add(tmp);
					tmp = "";
					marks = 0;
				} else {
					if (tmp.length() != 0) {
						added = true;
						vector.add(tmp);
						tmp = "";
						marks = 0;
					}
				}

				if (i == value.length() - 1) {
					if (addNull == true) {
						added = true;
						vector.add(tmp);
						tmp = "";
						marks = 0;
					} else {
						if (tmp.length() != 0) {
							added = true;
							vector.add(tmp);
							tmp = "";
							marks = 0;
						}
					}
				}
				// } else if (ch == '\'' || ch == '\"') {
				// marks++;
			} else {
				tmp += value.charAt(i);
			}
		}
		if (!added) {
			if (addNull == false) {
				if (tmp.length() != 0) {
					vector.add(tmp);
				}
			} else {
				vector.add(tmp);
			}
		}
		String[] result = new String[vector.size()];
		for (int i = 0; i < vector.size(); i++) {
			result[i] = vector.get(i).toString().trim();
		}
		return result;
	}

	/**
	 * 日期解析格式
	 * 
	 * @param string
	 * @return 文本对应是日期
	 * @throws Exception
	 */
	public Date stringToDate(String string) throws Exception {
		if (string == null) {
			return null;
		}
		SimpleDateFormat simpleDateFormat = new SimpleDateFormat(datePattern);
		try {
			return simpleDateFormat.parse(string);
		} catch (ParseException e) {
			throw new Exception("数据日期格式错误");
		}
	}

	public void addFileField(String fieldName) {
		fileFieldList.add(fieldName.toUpperCase());
	}

	public void addFileFields(String fieldNames) {
		String[] fields = fieldNames.split(",");
		for (int i = 0; i < fields.length; i++) {
			String fieldName = fields[i].toUpperCase();
			fileFieldList.add(fieldName);
		}
	}

	public void setStaticData(String key, String value) {
		staticData.put(key.toUpperCase(), value);
	}

	public String getStaticData(String key) {
		return staticData.get(key.toUpperCase());
	}

	public void addDataCheck(ImpDataBase dataCheck) {
		this.dataCheck = dataCheck;
	}

	public void setFileName(String fileName) {
		this.fileName = fileName;
	}

	public void setTableName(String tableName) {
		this.tableName = tableName;
	}

	public void setDatePattern(String datePattern) {
		this.datePattern = datePattern;
	}

	public void setPrtCount(int count) {
		prtCount = count;
	}

	public void setCharsetName(String charsetName) {
		this.charsetName = charsetName;
	}

	public void setSepertor(String sepertor) {
		this.sepertor = sepertor;
	}

}
